package com.beyond.util;

import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.beyond.dao.DataQueryDao;

public class DataVerifyUtil {

	/**
	 * 统计维度和指标为-1或0的记录数，将计算结果插入一个临时表的sql
	 * @param dimSql
	 * @param tagSql
	 * @param tbName
	 * @return
	 */
	public static String getNeedInsertCols(String dimSql,String tagSql,String valSql){
		String sql = "INSERT INTO temp_data_record(TABLENAME,RECORDTIME,TOTALRECORD,"+removeDateCol(dimSql)+","+DataVerifyUtil.getFieldCols(tagSql)+")   "+valSql+" ";
		
		return sql;
	}
	
	/**
	 * 组装插入临时表占总数比率的sql
	 * @param dimSql
	 * @param tagSql
	 * @param valSql
	 * @return
	 */
	public static String assembTotalRate(String dimSql,String tagSql,String valSql){
		String sql = "INSERT INTO temp_data_record(COMMOND,TABLENAME,RECORDTIME,TOTALRECORD,"+dimSql+","+tagSql+")  "+valSql;
		return sql;
	}
	
	/**
	 * 移除时间维的字段
	 * @param sql
	 * @return
	 */
	public static String removeDateCol(String sql){
		String[] strs = sql.split(",");
		String tmp_sql = "";
		for (String str : strs) {
			if( "minute_15_id".equals(str.trim().toLowerCase()))
				continue;
			else if("hour_id".equals(str.trim().toLowerCase()))
				continue;
			else if("day_id".equals(str.trim().toLowerCase()))
				continue;
			else if("CREATE_TIME".equals(str.trim()))
				continue;
			else
				tmp_sql += str+",";
		}
		return tmp_sql.substring(0,tmp_sql.length()-1);
	}
	
	/**
	 * 是否是时间维的字段
	 * @param sql
	 * @return
	 */
	public static boolean isDateCol(String str){
		if( "minute_15_id".equals(str.trim().toLowerCase()))
			return true;
		else if("hour_id".equals(str.trim().toLowerCase()))
			return true;
		else if("day_id".equals(str.trim().toLowerCase()))
			return true;
		else if("CREATE_TIME".equals(str.trim()))
			return true;
		else
			return false;
	}
	
	/**
	 * 将指标转换成对应的Field00模式
	 * @param cols
	 * @return
	 */
	public static String getFieldCols(String cols){
		StringBuilder sbs = new StringBuilder("");
		String[] dimSql = cols.split(",");
		for (String dim : dimSql) {
			if(dim.contains("0")){
				if(!DataVerifyUtil.isDateCol(dim)){
					String temp_dim = "field"+dim.substring(dim.indexOf("_")+1,dim.length());
					sbs.append(temp_dim).append(",");
				}
			}else{
				sbs.append(dim).append(",");
			}
		}
		
		return sbs.substring(0,sbs.length()-1);
	}
	
	/**
	 * 截取表名‘_’后的字符
	 * @param str
	 * @return
	 */
	public static String getlastChar(String str){
		if(StringUtils.isBlank(str))
			return null;
		
		return str.substring(str.lastIndexOf("_")+1,str.length()).toLowerCase();
	}
	
	/**
	 * 创建所有表的查询维度-1总和，并将计算结果插入一个临时表
	 * @return
	 */
	public static String createAllSql(String tbs,DataQueryDao queryDataDao){
		StringBuilder sb = new StringBuilder();
		String[] strs = tbs.split(",");
		Map<String,String> map = null;
		Map<String,String> map2 = new HashMap<String, String>();
		
		for (String tbName : strs) {
			map = queryDataDao.getColumnName(tbName.trim());
//			sb.append(createSqls(tbName, map.get("dimention"), map.get("target"),queryDataDao.getTabCount(tbName)));
			queryDataDao.update(createSqls(tbName, map.get("dimention"), map.get("target"),queryDataDao.getTabCount(tbName)));
//			sb.append(getColOfTotalRat(tbName, queryDataDao));//.append("\n\n");
			queryDataDao.update(getColOfTotalRat(tbName, queryDataDao));
		}
		
		return sb.toString();
	}
	
	/**
	 * 查询维度和指标为-1的个数
	 * @param tbs
	 * @param queryDataDao
	 * @return
	 */
	public static String createDimAndTagSql(String tbs,DataQueryDao queryDataDao){
		StringBuilder sb = new StringBuilder();
		String[] strs = tbs.split(",");
		Map<String,String> map = null;
		Map<String,String> map2 = new HashMap<String, String>();
		for (String tbName : strs) {
			map = queryDataDao.getColumnName(tbName.trim());
			sb.append(getDimAndTagCols(tbName, map.get("dimention"), map.get("target")));
		}
		
		return sb.toString();
	}
	
	/**
	 * 拼接求维度和指标为-1总和的sql，并将结果插入临时表
	 * @param tbName
	 * @param dimSql
	 * @param tagSql
	 * @return
	 */
	public static String createSqls(String tbName,String dimSql,String tagSql,int count){
		StringBuilder builder = new StringBuilder();
		String sql = "";
		// 求维度和指标为-1或0的记录数
		String lastChar = DataVerifyUtil.getlastChar(tbName);
		String date = "";
		String whereSql = "";
		String tag = lastChar.toLowerCase();
		if("15m".equals(tag)){
			date = DateUtil.getNowTime(tag); 
			whereSql = " where MINUTE_15_ID = "+date;
		}else if("h".equals(tag)){
			date = DateUtil.getNowTime(tag);
			whereSql = " where hour_id = "+date;
		}else if("d".equals(tag)){
			date = DateUtil.getNowTime(tag);
			whereSql = " where day_id = "+DateUtil.getNowTime(tag);;
		}
		
		builder.append("select '").append(tbName).append("',")
			.append(date).append(",").append(count).append(",").append(getSumCols(dimSql+","+tagSql))
			.append(" from ").append(tbName).append(whereSql);
		
		return getNeedInsertCols(dimSql, tagSql, builder.toString());
	}
	
	public static String getSumCols(String cols){
		StringBuilder sbs = new StringBuilder("");
		String[] dimSql = cols.split(",");
		for (String dim : dimSql) {
			if(dim.contains("0")){
				String temp_dim = "field"+dim.substring(dim.indexOf("_")+1,dim.length());
				sbs.append("SUM(CASE WHEN ").append(dim).append(" =-1 THEN 1 ELSE 0 END )").append(temp_dim).append(",");
			}else{
				if(!DataVerifyUtil.isDateCol(dim))
					sbs.append("SUM(CASE WHEN ").append(dim).append(" =-1 THEN 1 ELSE 0 END )").append(dim).append(",");
			}
		}
		return sbs.substring(0,sbs.length()-1);
	}
	
	/**
	 * 拼接求维度和指标为-1总和的sql
	 * @param tbName
	 * @param dimSql
	 * @param tagSql
	 * @return
	 */
	public static String getDimAndTagCols(String tbName,String dimSql,String tagSql){
		String lastChar = DataVerifyUtil.getlastChar(tbName);
		String whereSql = "";
		String date = "";
		String tag = lastChar.toLowerCase();
		if("15m".equals(tag)){
			date = DateUtil.getNowTime(tag); 
			whereSql = " where MINUTE_15_ID = "+date;
		}else if("h".equals(tag)){
			date = DateUtil.getNowTime(tag);
			whereSql = " where hour_id = "+date;
		}else if("d".equals(tag)){
			date = DateUtil.getNowTime(tag);
			whereSql = " where day_id = "+DateUtil.getNowTime(tag);;
		}
		
		String getSumSql = "select "+getOriginalSumCol(dimSql)+","+getOriginalSumCol(tagSql)+" from "+tbName;
		getSumSql += whereSql;
		
		return getSumSql;
	}
	
	/**
	 * ETL统计各表记录数Sql
	 * @param map
	 * @return
	 */
	public static String getEtlTabCountSql(String tabs){
		if(StringUtils.isBlank(tabs))
			return null;
		StringBuilder sb = new StringBuilder();
		String[] strs = tabs.split(",");
		for (String tab : strs) {
			sb.append(getTabCount(tab));
		}
		return sb.toString();
	}
	
	/**
	 * ETL汇聚，统计各表的记录数sql
	 * @param tabName
	 * @param queryDataDao
	 * @return
	 */
	public static String getTabCount(String tabName){
		String pre_tab = "";
		String tab_h = "",tab_d = "",tab_15m = "";
		String rsTab = "";
		String sql = " select count(1)"+tabName+" from  ";
		DataQueryDao dao = new DataQueryDao();
		
		Map<String,String> map = new HashMap<String, String>();
		if(tabName != null && !"".equals(tabName)){
			pre_tab = tabName.substring(0,tabName.lastIndexOf("_")+1);
			String sub_tab = tabName.substring(tabName.lastIndexOf("_")+1,tabName.length()).toLowerCase();
			String updatesql = "";
			if("15m".equals(sub_tab)){
				tab_15m = sql+tabName+";\n";
				tab_h = sql+pre_tab+"H;\n";
				tab_d = sql+pre_tab+"D;\n";
				updatesql = "INSERT INTO temp_data_count VALUES(seqforcounter.nextval,to_char(SYSDATE,'yyyy-MM-dd hh:mm:ss'),'"
						+tabName+"',"+dao.getTabCount(tabName)+","
						+dao.getTabCount(pre_tab+"H")+","
						+dao.getTabCount(pre_tab+"D")+")";
				
				rsTab = tab_15m+tab_h+tab_d+"\n";
			}else if("h".equals(sub_tab)){
				tab_h = sql+tabName+";\n";
				tab_d = sql+pre_tab+"D;\n";
				rsTab = tab_h+tab_d+"\n";
				updatesql = "INSERT INTO temp_data_count VALUES(seqforcounter.nextval,to_char(SYSDATE,'yyyy-MM-dd hh:mm:ss'),'"
					+tabName+"',0,"
					+dao.getTabCount(pre_tab+"H")+","
					+dao.getTabCount(pre_tab+"D")+")";
			}
//			dao.update(updatesql);
		}
		return rsTab;
	}
	
	/**
	 * 根据输入的标示，获取表名
	 * 15m,h,d 
	 * 15m 判断是否存在
	 * h 判断是否存在，不存在将15m替换成H
	 * d 判断是否存在，不存在将15替换成D
	 * @param strs
	 * @return
	 */
	public static String getTagTable(String strs,String tag){
		String h_tab = "";
		if(StringUtils.isBlank(strs))
			return null;
		tag = tag.toLowerCase();
		String[] tabs = strs.split(",");
		for (String str : tabs) {
			if("15m".equals(tag)){
				if("15m".equals(getlastChar(str))){
					h_tab  += str+",";
				}
			}else if("h".equals(tag)){
				if("h".equals(getlastChar(str)))
					h_tab += str+",";
				else
					h_tab += str.substring(0,str.lastIndexOf("_")+1)+"H"+",";
			}else if("d".equals(tag)){
				if("d".equals(getlastChar(str)))
					h_tab += str+",";
				else
					h_tab += str.substring(0,str.lastIndexOf("_")+1)+"D"+",";
			}
		}
		return h_tab.substring(0,h_tab.length()-1);
	}
	
	/**
	 * 获取原生态列名的求和sql
	 * @param sql
	 * @param tabName
	 * @return
	 */
	public static String getOriginalSumCol(String sql){
		if(StringUtils.isBlank(sql))
			return null;
		
		StringBuilder sbs = new StringBuilder("");
		String[] cols = sql.split(",");
		for (String col : cols) {
			sbs.append(" SUM(CASE WHEN ").append(col).append(" =-1 THEN 1 ELSE 0 END )").append(col).append(",");
		}
		String rs = sbs.substring(0,sbs.length()-1);
		
		return rs;
	}
	
	/**
	 * 截取指定开始位置和结束位置字符
	 * @param str
	 * @param start
	 * @param end
	 * @return
	 */
	public static String getSubstring(String str,int start,int end){
		if(StringUtils.isBlank(str))
			return null;
		return str.substring(start,end);
	}
	
	/**
	 * 所有表各维度和指标占总数的比率
	 * 
	 * @param tabNames 
	 * @param dataQueryDao
	 * @return
	 */
	public static String getColOfTotalRat(String tabNames,DataQueryDao dataQueryDao){
		if(StringUtils.isBlank(tabNames))
			return null;
		String sqls = "";
		String error_tab = "";
		String[] tabs = tabNames.split(",");
		for (String tab : tabs) {
			int total 	= dataQueryDao.getTabCount(tab);
			Map<String, String> map = dataQueryDao.getColumnName(tab);
			String dimSql = removeDateCol(map.get("dimention"));
			String tagSql = map.get("target");
			String sql = getDimAndTagCols(tab, dimSql, tagSql);
//			list.add(dataQueryDao.getTotalRat(tab, dimSql+","+tagSql, sql, total));
			String temp = assembValSql(dataQueryDao.getTotalRat(tab, dimSql+","+tagSql, sql, total), dimSql+","+tagSql,tab,total);
			if(StringUtils.isBlank(temp)){
				error_tab += ",";
				continue;
			}
			sqls += "INSERT INTO temp_data_record(COMMOND,TABLENAME,RECORDTIME,TOTALRECORD,"+temp;
		}
		
//		System.out.println("Exceptions: "+error_tab);
		return sqls;
	}
	
	/**
	 * 根据维度获取维度对应的百分值，组装成一个sql， 用于将维度和指标的值插入数据库临时表
	 * @param map
	 * @param cols
	 * @return
	 */
	public static String assembValSql(Map<String,Double> map,String cols,String tabName,int total){
		
		String dimSql = "";
		String tagSql = "";
		String sql = "";
		if(null != map && map.size()>1){
			for (Map.Entry<String, Double> obj : map.entrySet()) {
				for(String col : cols.split(",")){
					if(col.equals(obj.getKey())){
						dimSql += obj.getKey()+",";
						tagSql += obj.getValue()+",";
					}
				}
			}
			
				
			dimSql = getFieldCols(dimSql.substring(0,dimSql.length()-1));
			tagSql = tagSql.substring(0,tagSql.length()-1);
			sql = dimSql+") values ('维度和指标占总数比率','"+tabName+"_rate','"+getTimeByTag(tabName)+"',"+total+","+tagSql+")";
		}
		return sql;
	}
	
	/**
	 * 获取时间
	 * @param tabName
	 * @return
	 */
	public static String getTimeByTag(String tabName){
		String tag = DataVerifyUtil.getlastChar(tabName).toLowerCase();
		String date = "";
		if("15m".equals(tag)){
			date = DateUtil.getNowTime(tag); 
		}else if("h".equals(tag)){
			date = DateUtil.getNowTime(tag);
		}else if("d".equals(tag)){
			date = DateUtil.getNowTime(tag);
		}
		return date;
	}
	
	/**
	 * 将统计的比率数插入数据库中
	 * @param dao
	 * @param tbs
	 * @param tag
	 */
	public static String putTotalRatIntoTempRecord(DataQueryDao dao,String tbs,String tag){
		String tabs = getTagTable(tbs,tag);
		String sqls = getColOfTotalRat(tabs,dao);
		StringBuilder sbd = new StringBuilder();
		/*if(StringUtils.isBlank(sqls))
			return null;*/
		
		String[] sqlArray = sqls.split(";");
		for (String sql : sqlArray) {
//			dao.update(sql);
			sbd.append(sql+";\n");
		}
		return sbd.toString();
	}
	
	/**
	 * Get all table's fields and remove the same field
	 * @param tabs
	 * @return
	 */
	public static String getAllFields(String cols){
		if(StringUtils.isBlank(cols))
			return null;
		String new_tabs = "";
		String[] array = cols.split(",");
		for(String tab : array){
			if(new_tabs.contains(tab))
				continue;
			else
				new_tabs += tab+",";
		}
		return new_tabs;
	}
	
	/**
	 * 获取表中所有维度字段并去重
	 * @param tabs
	 * @param dao
	 * @return
	 */
	public static String getAllTablesField(String tabs,DataQueryDao dao){
		if(StringUtils.isBlank(tabs))
			return null;
		String allCols = "";
		String[] array = tabs.split(",");
		for(String tab : array){
			allCols += dao.getColumnName(tab).get("dimention")+",";
		}
		
		return getAllFields(allCols);
	}
	
	public static void main(String[] args) throws Exception {
		DataQueryDao dao = new DataQueryDao();
		String tbs = "F_PS_GDS01_GPRS_15M,F_PS_GDS01_GPRS_CAUSE_15M,F_PS_GDS01_2G_CELL_15M,F_PS_GDS01_3G_SAC_15M,F_PS_GDS01_2G_CELL_CAUSE_15M,F_PS_GDS01_3G_SAC_CAUSE_15M,F_PS_GDS01_2G_GPRS_CAUSE_15M,F_PS_GDS01_3G_GPRS_CAUSE_15M,F_PS_GDS01_2G_TERMINAL_15M,F_PS_GDS01_3G_TERMINAL_15M,F_PS_GDS01_2G_USER_15M,F_PS_GDS01_3G_USER_15M,F_PS_GDS02_SERV_15M,F_PS_GDS02_2G_CELL_15M,F_PS_GDS02_3G_SAC_15M,F_PS_GDS02_2G_TERMINAL_H,F_PS_GDS02_3G_TERMINAL_H,F_PS_GDS02_SERV_TERMINAL_H,F_PS_GDS02_SERV_USER_H,F_PS_GDS02_2G_APN_USER_H,F_PS_GDS02_3G_APN_USER_H,F_PS_GDS03_GPRS_15M,F_PS_GDS03_GPRS_CAUSE_15M,F_PS_GDS03_2G_CELL_15M,F_PS_GDS03_3G_SAC_15M,F_PS_GDS03_2G_CELL_CAUSE_15M,F_PS_GDS03_3G_SAC_CAUSE_15M,F_PS_GDS03_2G_CELL_TERM_15M,F_PS_GDS03_3G_SAC_TERM_15M,F_PS_GDS03_2G_TERMINAL_15M,F_PS_GDS03_3G_TERMINAL_15M,F_PS_GDS03_TERMINAL_CAUSE_15M,F_PS_GDS03_TERMINAL_HOST_H,F_PS_GDS03_SP_CAUSE_15M,F_PS_GDS03_SP_OPERATOR_H,F_PS_GDS03_SP_H,F_PS_GDS03_2G_CELL_SP_H,F_PS_GDS03_3G_SAC_SP_H,F_PS_GDS03_SP_HOST_OPT_H,F_PS_GDS03_2G_USER_H,F_PS_GDS03_3G_USER_H,F_PS_GDS03_USER_SP_H,F_PS_GDS04_GPRS_15M,F_PS_GDS04_GPRS_CAUSE_15M,F_PS_GDS04_2G_CELL_15M,F_PS_GDS04_3G_SAC_15M,F_PS_GDS04_2G_CELL_CAUSE_15M,F_PS_GDS04_3G_SAC_CAUSE_15M,F_PS_GDS04_2G_TERMINAL_15M,F_PS_GDS04_3G_TERMINAL_15M,F_PS_GDS05_GPRS_15M,F_PS_GDS05_GPRS_CAUSE_15M,F_PS_GDS05_2G_CELL_15M,F_PS_GDS05_3G_SAC_15M,F_PS_GDS05_2G_CELL_CAUSE_15M,F_PS_GDS05_3G_SAC_CAUSE_15M,F_PS_GDS05_2G_TERMINAL_15M,F_PS_GDS05_3G_TERMINAL_15M,F_PS_GDS06_GPRS_15M,F_PS_GDS06_GPRS_CAUSE_15M";
		long startTime = System.currentTimeMillis();
		
		System.out.println("开始生成文件");
		// 将ETL各表的总数插入临时表：temp_data_count
//		FileUtil.createFile("C:\\sql\\etl_tab_count.sql", getEtlTabCountSql(tbs));
		
		// 统计维度和指标占总数的百分比
//		String[] strs = {"15m","H","D"};
		String[] strs = {"15m"};
		for (String tag : strs) {
//			String tab_ = putTotalRatIntoTempRecord(dao, tbs, tag);
//			FileUtil.createFile("C:\\sql\\"+tag+"_rate.sql",tab_);
			FileUtil.createFile("C:\\sql\\"+tag+"_total.sql", createAllSql(getTagTable(tbs,tag),dao));
		}
		System.out.println("结束生成文件");
		System.out.println("所用时间："+(System.currentTimeMillis()-startTime));
		
//		System.out.println(d1);
		// 获取表的维度字段
//		System.out.println(getAllTablesField(tbs,dao));
		
	}
}
